package com.glowdayz.glowmee.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import com.glowdayz.common.DBHandler;
import com.glowdayz.common.DateTime;
import com.glowdayz.common.PageHandler;
import com.glowdayz.common.MultipartBox.FileInfo;
import com.glowdayz.glowmee.cmd.PouchCommentCMD;

public class PouchCommentDAO {
	
static Logger logger = Logger.getLogger(PouchCommentDAO.class);
	
	private static PouchCommentDAO instance = null;
	
	DBHandler db = null;
	
	public static PouchCommentDAO getInstance() {
		if (instance == null) {
			instance = new PouchCommentDAO();
		}
		return instance;
	}
	
	public PouchCommentDAO() {
		db = DBHandler.getInstance();
	}
	
	public int insertPouchComment(PouchCommentCMD cmd) throws SQLException {
		logger.debug("[insertPouchComment] ---------- Method Start ----------");
		logger.debug("[insertPouchComment][PARAM] EventCommentCMD cmd=" + cmd.toString());
		
		int result = 0;
		
		PreparedStatement pstmt = null;
		
		DBHandler.getInstance().beginTrans();
		
		int readCnt = 0, recommendCnt = 0, commentCnt = 0;
		String selectSql = "SELECT p.readCount, p.recommendCount, (SELECT COUNT(*) FROM pouchcomment c WHERE p.idPouch=c.idPouch) "
				+ "FROM pouch p "
				+ "WHERE p.idPouch=?;";
		pstmt = DBHandler.getInstance().getPrepareStatement(selectSql);
		try {
			pstmt.setInt(1, cmd.getIdPouch());
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				if (rs.next()) {
					readCnt = rs.getInt(1);
					recommendCnt = rs.getInt(2);
					commentCnt = rs.getInt(3);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[getEditerPouch] " + e.getLocalizedMessage());
			logger.error("[getEditerPouch] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		int pouchScore = (readCnt * 1) + (recommendCnt * 10) + ((commentCnt + 1) * 5);
		
		String updateSql = "UPDATE pouch "
				+ "SET pouchScore=? "
				+ "WHERE idPouch=?;";
		
		pstmt = db.getPrepareStatement(updateSql);
		
		try {
			pstmt.setInt(1, pouchScore);
			pstmt.setInt(2, cmd.getIdPouch());
			result = pstmt.executeUpdate();
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[wishListPouch] " + e.getLocalizedMessage());
			logger.error("[wishListPouch] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result < 1) {
			DBHandler.getInstance().rollback();
			return 0;
		}
		
		String sql = "INSERT INTO pouchcomment("
				+ "idRegister, idPouch, pouchComment, isDisplay, isPushConfirm, create_date"
				+ ") VALUES (?, ?, ?, 1, 0, ?)";
		
		pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, cmd.getIdRegister());
			pstmt.setInt(2, cmd.getIdPouch());
			pstmt.setString(3, cmd.getPouchComment());
			pstmt.setString(4, DateTime.getInstance().getNowDateTimeString());
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[insertPouchComment] " + e.getLocalizedMessage());
			logger.error("[insertPouchComment] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[insertPouchComment][RETURN] result=" + result);
		logger.debug("[insertPouchComment] ---------- Method End ----------");
		
		return result;
	}
	
	public int isDisplayComment(PouchCommentCMD cmd) throws SQLException {
		logger.debug("[isDisplayComment] ---------- Method Start ----------");
		logger.debug("[isDisplayComment][PARAM] PouchCommentCMD cmd=" + cmd.toString());
		
		int result = 0;
		
		String sql = "UPDATE pouchcomment "
				+ "SET isDisplay=? "
				+ "WHERE idPouchComment=?";
		PreparedStatement pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, cmd.getIsDisplay());
			pstmt.setInt(2, cmd.getIdPouchComment());
			
			DBHandler.getInstance().beginTrans();
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[isDisplayComment] " + e.getLocalizedMessage());
			logger.error("[isDisplayComment] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[isDisplayComment][RETURN] result=" + result);
		logger.debug("[isDisplayComment] ---------- Method End ----------");
		
		return result;
	}
	
	public ArrayList<PouchCommentCMD> getCommentList(int page, int count, String where) throws Exception {
		logger.debug("[getCommentList] ---------- Method Start ----------");
		logger.debug("[getCommentList][PARAM] page=" + page);
		logger.debug("[getCommentList][PARAM] count=" + count);
		logger.debug("[getCommentList][PARAM] where=" + where);
		
		ArrayList<PouchCommentCMD> result = null;
		int cnt = db.getCount("pouchcomment p, register r", "*", "p.idRegister=r.idRegister AND " + where);
		
		if (count == -1) {
			count = cnt;
		}
		
		String sql = "SELECT p.idPouchComment, p.idRegister, r.nickName, p.idPouch, p.pouchComment, p.isDisplay, p.create_date "
					+ "FROM pouchcomment p, register r "
					+ "WHERE p.idRegister=r.idRegister AND " + where + " "
					+ "ORDER BY p.create_date DESC "
					+ "LIMIT ?, ?";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		if (cnt == 0) {
			PageHandler.getInstance(page, cnt);
		} else {
			// 페이징 처리 --> 한 페이지에 count개씩, 페이지번호는 10개 출력
			PageHandler.getInstance(page, cnt, count, 10);
		}
		
		try {
			pstmt.setInt(1, PageHandler.getInstance().getStartRec());
			pstmt.setInt(2, PageHandler.getInstance().getPageArticleCount());
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<PouchCommentCMD>();
				
				while(rs.next()) {
					int idPouchComment = rs.getInt(1);
					int idRegister = rs.getInt(2);
					String nickName = rs.getString(3);
					int idPouch = rs.getInt(4);
					String pouchComment = rs.getString(5);
					int isDisplay = rs.getInt(6);
					String create_date = rs.getString(7);
					
					FileInfo image = this.getImage(idRegister);
					
					PouchCommentCMD item = new PouchCommentCMD(idPouchComment, idRegister, nickName, idPouch, pouchComment, isDisplay, create_date, image);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getCommentList] " + e.getLocalizedMessage());
			logger.error("[getCommentList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
		}
		
		if (result != null) {
			logger.debug("[getCommentList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getCommentList] ---------- Method End ----------");
		
		return result;
	}
	
	public ArrayList<PouchCommentCMD> getCommentXmlList(int page, int count, int id) throws Exception {
		logger.debug("[getCommentXmlList] ---------- Method Start ----------");
		
		PreparedStatement pstmt = null;
		
		String update = "UPDATE pouch "
				+ "SET readCount=(readCount+1) "
				+ "WHERE idPouch=?";
		
		pstmt = db.getPrepareStatement(update);
		
		try {
			pstmt.setInt(1, id);
			pstmt.executeUpdate();
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			e.getLocalizedMessage();
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		ArrayList<PouchCommentCMD> result = null;
		int cnt = db.getCount("pouchcomment p, register r, commoncode c", "*", 
				"p.idRegister=r.idRegister AND r.skinType=c.idCommon AND p.isDisplay=1 AND p.idPouch=" + id);
		
		if (count == -1) {
			count = cnt;
		}
		
		String sql = "SELECT p.idPouchComment, p.idRegister, r.nickName, r.birthYear, c.commonText, p.pouchComment, p.create_date "
					+ "FROM pouchcomment p, register r, commoncode c "
					+ "WHERE p.idRegister=r.idRegister AND r.skinType=c.idCommon AND p.isDisplay=1 AND p.idPouch=? "
					+ "ORDER BY p.create_date DESC "
					+ "LIMIT ?, ?";
		
		pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		if (cnt == 0) {
			PageHandler.getInstance(page, cnt);
		} else {
			// 페이징 처리 --> 한 페이지에 count개씩, 페이지번호는 10개 출력
			PageHandler.getInstance(page, cnt, count, 10);
		}
		
		try {
			pstmt.setInt(1, id);
			pstmt.setInt(2, PageHandler.getInstance().getStartRec());
			pstmt.setInt(3, PageHandler.getInstance().getPageArticleCount());
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<PouchCommentCMD>();
				
				while(rs.next()) {
					int idPouchComment = rs.getInt(1);
					int idRegister = rs.getInt(2);
					String nickName = rs.getString(3);
					int birthYear = rs.getInt(4);
					String skinType = rs.getString(5);
					String pouchComment = rs.getString(6);
					String create_date = rs.getString(7);
					
					FileInfo image = this.getImage(idRegister);
					
					PouchCommentCMD item = new PouchCommentCMD(idPouchComment, idRegister, nickName, pouchComment, create_date, image, birthYear, skinType);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getCommentXmlList] " + e.getLocalizedMessage());
			logger.error("[getCommentXmlList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
		}
		
		if (result != null) {
			logger.debug("[getCommentXmlList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getCommentXmlList] ---------- Method End ----------");
		
		return result;
	}
	
	public FileInfo getImage(int idx) throws SQLException {
		String fileSql = "SELECT fileOrgName, idAttacheFile, fileSaveName, fileDir, fileSize, fileType, sortKey "
				+ "FROM attachefile "
				+ "WHERE tableName='register' AND tableKey=?";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(fileSql);
		FileInfo info = null;
		
		try {
			pstmt.setInt(1, idx);
			ResultSet rs = pstmt.executeQuery();
			if (rs != null) {
				while(rs.next()) {
					String fileOrgName = rs.getString(1);
					int idAttacheFile = rs.getInt(2);
					String fileSaveName = rs.getString(3);
					String fileDir = rs.getString(4);
					int fileSize = rs.getInt(5);
					String fileType = rs.getString(6);
					int sortKey = rs.getInt(7);
					
					info = new FileInfo("", idAttacheFile, fileOrgName, fileSaveName, fileDir, fileSize, fileType, sortKey);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error(e.getLocalizedMessage());
			logger.error(e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		return info;
	}
	
}
